package com.example.demo.mapper;

import com.example.demo.model.User;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * 用户数据访问接口
 */
@Mapper
public interface UserMapper {
    
    @Select("SELECT * FROM users WHERE user_id = #{userId}")
    User findById(@Param("userId") Integer userId);
    
    @Select("SELECT * FROM users WHERE usercode = #{usercode}")
    User findByUsercode(@Param("usercode") String usercode);
    
    @Select("SELECT * FROM users WHERE email = #{email}")
    User findByEmail(@Param("email") String email);
    
    @Select("SELECT * FROM users WHERE org_id = #{orgId}")
    List<User> findByOrgId(@Param("orgId") Integer orgId);
    
    @Select("SELECT * FROM users WHERE user_type = #{userType}")
    List<User> findByUserType(@Param("userType") String userType);
    
    @Select("SELECT * FROM users WHERE org_id = #{orgId} AND user_type = #{userType}")
    List<User> findByOrgIdAndUserType(@Param("orgId") Integer orgId, @Param("userType") String userType);
    
    @Select("SELECT * FROM users WHERE user_type = #{userType} " +
            "AND JSON_EXTRACT(extra_info, '$.approval_status') = #{approvalStatus}")
    List<User> findByApprovalStatus(@Param("userType") String userType, @Param("approvalStatus") String approvalStatus);
    
    @Select("SELECT * FROM users")
    List<User> findAll();
    
    @Insert("INSERT INTO users(usercode, password_hash, email, full_name, user_type, org_id, extra_info, is_active) " +
            "VALUES(#{usercode}, #{passwordHash}, #{email}, #{fullName}, #{userType}, #{orgId}, #{extraInfo}, #{isActive})")
    @Options(useGeneratedKeys = true, keyProperty = "userId")
    int insert(User user);
    
    @Update("UPDATE users SET usercode = #{usercode}, " +
            "password_hash = #{passwordHash}, email = #{email}, " +
            "full_name = #{fullName}, user_type = #{userType}, " +
            "org_id = #{orgId}, extra_info = #{extraInfo}, is_active = #{isActive} " +
            "WHERE user_id = #{userId}")
    int update(User user);
    
    @Delete("DELETE FROM users WHERE user_id = #{userId}")
    int deleteById(@Param("userId") Integer userId);

    /**
     * 根据组织ID及其所有子组织查询用户
     */
    @Select("WITH RECURSIVE org_tree AS (" +
            "  SELECT org_id, parent_id, name, org_type FROM organizations WHERE org_id = #{orgId} " +
            "  UNION ALL " +
            "  SELECT o.org_id, o.parent_id, o.name, o.org_type " +
            "  FROM organizations o " +
            "  INNER JOIN org_tree ot ON o.parent_id = ot.org_id" +
            ") " +
            "SELECT DISTINCT u.* FROM users u " +
            "INNER JOIN org_tree ot ON u.org_id = ot.org_id " +
            "WHERE u.user_type = #{userType}")
    List<User> findByOrgTreeAndUserType(@Param("orgId") Integer orgId, @Param("userType") String userType);
} 